﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using Microsoft.Office.Interop.Excel;

namespace Sicat.Aten.Localization.Test
{
    public class ExcelFile
    {
        public string FilePath { get; private set; }
        public ExcelFile(string filePath)
        {
            FilePath = filePath;
        }

        object excelApp;
        object excelBooks;
        object excelBook;
        object excelSheets;

        public void Close()
        {
            ExcelReflectionUtil.CloseProcess(excelApp);
        }

        public void Save()
        {
            //xlExcel8 97
            object format = ExcelReflectionUtil.GetEnum("XlFileFormat", "xlExcel12");
            ExcelReflectionUtil.InvokeMethod(excelBook, "SaveAs", FilePath, Missing.Value);
        }

        public void Open()
        {
            // Start an Excel-Application in the background
            excelApp = ExcelReflectionUtil.GetExcelApp();

            // Auflistung der Mappeen abrufen
            excelBooks = ExcelReflectionUtil.GetPropertyValue(excelApp, "Workbooks");
            if (File.Exists(FilePath))
                excelBook = ExcelReflectionUtil.InvokeMethod(excelBooks, "Open", FilePath);
            else
            {
                object XlWBATemplate_xlWBATWorksheet = ExcelReflectionUtil.GetEnum("XlWBATemplate", "xlWBATWorksheet");
                excelBook = ExcelReflectionUtil.InvokeMethod(excelBooks, "Add", XlWBATemplate_xlWBATWorksheet);
            }
            // Auflistung der Tabellenblätter abrufen
            excelSheets = ExcelReflectionUtil.GetPropertyValue(excelBook, "Sheets");
        }

        public object GetSheet(int index)
        {
            return ExcelReflectionUtil.GetPropertyValue(excelBook, "Worksheets", 1);
        }

        public object AddSheet(string name)
        {
            // Neues Tabellenblatt erstellen
            object sheet = ExcelReflectionUtil.InvokeMethod(excelSheets, "Add");
            // Name des Tabellenblatts festlegen
            ExcelReflectionUtil.SetPropertyValue(sheet, "Name", name);
            return sheet;
        }

        public object AddDropdown(object sheet, int cellRow, int cellCol, List<string> items)
        {
            var flatList = string.Join(",", items.ToArray());

            object cell = ExcelReflectionUtil.GetPropertyValue(sheet, "Cells", cellRow, cellCol);
            object validation = ExcelReflectionUtil.GetPropertyValue(cell, "Validation");
            ExcelReflectionUtil.InvokeMethod(validation, "Delete");

            

            object XlDVType_xlValidateList = ExcelReflectionUtil.GetEnum("XlDVType", "xlValidateList");
            object XlDVAlertStyle_xlValidAlertInformation = ExcelReflectionUtil.GetEnum("XlDVAlertStyle", "xlValidAlertInformation");
            object XlFormatConditionOperator_xlBetween = ExcelReflectionUtil.GetEnum("XlFormatConditionOperator", "xlBetween");

            ExcelReflectionUtil.InvokeMethod(validation, "Add", XlDVType_xlValidateList, XlDVAlertStyle_xlValidAlertInformation, XlFormatConditionOperator_xlBetween, flatList, Type.Missing);

            object XlRangeValueDataType_xlRangeValueDefault = ExcelReflectionUtil.GetEnum("XlRangeValueDataType", "xlRangeValueDefault");

            //var type = ExcelReflectionUtil.GetExcelType("_Worksheet");
            //var mi = type.GetMethod("get_Range");
            //var range = mi.Invoke(sheet, new object[]{"C2:C3", Type.Missing});
            //mi = ExcelReflectionUtil.GetExcelType("Range").GetMethod("set_Value");
            //mi.Invoke(range, new object[] { XlRangeValueDataType_xlRangeValueDefault, items[0] });
            //var condi = ExcelReflectionUtil.GetPropertyValue(range, "FormatConditions");

            //mi = ExcelReflectionUtil.GetExcelType("FormatConditions").GetMethod("AddColorScale");
            //var cfColorScale = mi.Invoke(condi, new object[]{2});
            ////var cfColorScale = ExcelReflectionUtil.InvokeMethod(condi, "AddColorScale", 1);
            //var colorScaleCriterion = ExcelReflectionUtil.GetPropertyValue(cfColorScale, "ColorScaleCriteria", 1);
            //var formatColor = ExcelReflectionUtil.GetPropertyValue(colorScaleCriterion, "FormatColor");
            //ExcelReflectionUtil.SetPropertyValue(formatColor, "Color", 0x000000FF);

            //colorScaleCriterion = ExcelReflectionUtil.GetPropertyValue(cfColorScale, "ColorScaleCriteria", 2);
            //formatColor = ExcelReflectionUtil.GetPropertyValue(colorScaleCriterion, "FormatColor");
            //ExcelReflectionUtil.SetPropertyValue(formatColor, "Color", 0x00FF0000);

            Worksheet ws = sheet as Worksheet;
            ws.get_Range("C2", Type.Missing).set_Value(XlRangeValueDataType.xlRangeValueDefault, "a");
            ws.get_Range("C3", Type.Missing).set_Value(XlRangeValueDataType.xlRangeValueDefault, "b");


            ColorScale cfColorScale = (ColorScale)(ws.get_Range("C2:C3", Type.Missing).FormatConditions.AddColorScale(2));
            cfColorScale.ColorScaleCriteria[1].FormatColor.Color = 0x000000FF;  // Red
            cfColorScale.ColorScaleCriteria[2].FormatColor.Color = 0x00FF0000;  // Blue
             
            //sheet.GetType().InvokeMember("Cells", BindingFlags.InvokeMethod | BindingFlags.OptionalParamBinding, null, sheet, new object[] { cellRow, cellCol, XlRangeValueDataType_xlRangeValueDefault, items[0] });
            //ExcelReflectionUtil.InvokeMethod(cell, "set_Value", XlRangeValueDataType_xlRangeValueDefault, items[0]);

            return validation;
        }

        public void SetCellValue(object sheet, int cellRow, int cellCol, object value)
        {
            ExcelReflectionUtil.SetPropertyValue(sheet, "Cells",value, cellRow, cellCol);
        }

        public static void CellsChange(object Range)//Excel.Range
        {
            //MessageBox.Show("sfdsf");
            //object style = ExcelReflectionUtil.GetPropertyValue(Range, "Style");
            //ExcelReflectionUtil.SetPropertyValue(style, "KnownColor", Colors.Red);

            //MessageBox.Show(ExcelReflectionUtil.GetPropertyValue(style, "KnownColor").ToString());
        }


        public void AddCellsChangeEventHandler(object sheet, Action<object> handler)
        {
            var eventInfo = ExcelReflectionUtil.GetExcelType("DocEvents_Event").GetEvent("Change");
            Type eventType = eventInfo.EventHandlerType;
            //Type type = ExcelReflectionUtil.GetExcelType("Worksheet");
            MethodInfo methodInfo = this.GetType().GetMethod("CellsChange");// type.GetMethod("ChangeEvent");
            Delegate d = Delegate.CreateDelegate(eventType, methodInfo);
            //EventInfo eventInfo = excelApp.GetType().GetEvent("SheetSelectionChange");
            //var eventInfo = sheet.GetType().GetEvents();
                //ExcelReflectionUtil.GetExcelType("Worksheet").GetEvents();// GetEvent("Change");
            //CellsChangeHandler cellsChangeHandler = new CellsChangeHandler(CellsChange);
            ExcelReflectionUtil.InvokeMethod(eventInfo, "AddEventHandler", sheet, d);

            //ExcelReflectionUtil.SetPropertyValue(excelApp, "Visible", true);
            //ExcelReflectionUtil.SetPropertyValue(excelApp, "UserControl", true);

            //CellsChangeHandler cellsChangeHandler = new CellsChangeHandler(CellsChange);
            //object EventDel_CellsChange = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.DocEvents_ChangeEventHandler"), obj => CellsChange(obj));
        }

        //public void SetRangeValue(object sheet, int top_row, int top_col, int bottom_row, int bottom_col,  object value)
        //{
           
        //    sheet.Range["D8"].Text = "Country";
        //    //set the datasource of the E8
        //    sheet.Range["X1"].Text = "Lucy";
        //    sheet.Range["X2"].Text = "Bolivia";
        //    sheet.Range["X3"].Text = "Brazil";
        //    sheet.Range["X4"].Text = "Canada";
        //    sheet.Range["X5"].Text = "Chile";
        //    sheet.Range["X6"].Text = "Colombia";
        //    sheet.Range["X7"].Text = "Cuba";
        //    sheet.Range["X8"].Text = "Ecuador";
        //    sheet.Range["X9"].Text = " Salvado";
        //    sheet.Range["X10"].Text = "Guyana";
        //    sheet.Range["X11"].Text = "Jamaicatates";
        //    sheet.Range["X12"].Text = "Mexico";
        //    sheet.Range["X13"].Text = "Nicaragua";
        //    sheet.Range["X14"].Text = "Paraguay";
        //    sheet.Range["X15"].Text = "Peru";
        //    sheet.Range["X16"].Text = "United";
        //    sheet.Range["X17"].Text = "Uruguay";
        //    sheet.Range["X18"].Text = "Venezuel";

        //    CellRange rangeName = sheet.Range["E8"];
        //    rangeName.DataValidation.AllowType = CellDataType.Formula;
        //    rangeName.DataValidation.DataRange = sheet.Range["X1:X18"];
        //    rangeName.DataValidation.IgnoreBlank = true;
        //    rangeName.Activate();
        //    //hide column X                                 
        //    sheet.HideColumn(sheet.Range["X1"].Column);

        //    sheet.Range["D10"].Text = "Capital";
        //    //set the datasource of the E9
        //    sheet.Range["Y1"].Text = "Buenos Aires";
        //    sheet.Range["Y2"].Text = "La Paz";
        //    sheet.Range["Y3"].Text = "Brasilia";
        //    sheet.Range["Y4"].Text = "Ottawa";
        //    sheet.Range["Y5"].Text = "Santiago";
        //    sheet.Range["Y6"].Text = "Bagota";
        //    sheet.Range["Y7"].Text = "Havana";
        //    sheet.Range["Y8"].Text = "Quito";
        //    sheet.Range["Y9"].Text = "San Salvador";
        //    sheet.Range["Y10"].Text = "Georgetown";
        //    sheet.Range["Y11"].Text = "Kingston";
        //    sheet.Range["Y12"].Text = "Mexico City";
        //    sheet.Range["Y13"].Text = "Managua";
        //    sheet.Range["Y14"].Text = "Asuncion";
        //    sheet.Range["Y15"].Text = "Lima";
        //    sheet.Range["Y16"].Text = "Washington";
        //    sheet.Range["Y17"].Text = "Montevideo";
        //    sheet.Range["Y18"].Text = "Caracas";
        //    //Binding the datasource                 
        //    CellRange rangeCapital = sheet.Range["E10"];
        //    rangeCapital.DataValidation.AllowType = CellDataType.Formula;
        //    rangeCapital.DataValidation.DataRange = sheet.Range["Y1:Y18"];
        //    rangeCapital.DataValidation.IsSuppressDropDownArrow = false;
        //    rangeCapital.Activate();
        //    //hide coulumn Y
        //    sheet.HideColumn(sheet.Range["Y1"].Column);

        //    sheet.Range["D12"].Text = "Continent";
        //    //set the datasource of the F9
        //    CellRange rangeContinent = sheet.Range["E12"];
        //    rangeContinent.DataValidation.AllowType = CellDataType.Formula;
        //    rangeContinent.DataValidation.Values = new String[] { "South America", "North America" };
        //    rangeContinent.DataValidation.IsSuppressDropDownArrow = false;
        //    rangeContinent.Activate();

        //    //Set the column's width
        //    sheet.Range["E8"].Text = "Please select the country";
        //    sheet.Range["E10"].Text = "Please select the capital";
        //    sheet.Range["E12"].Text = "Please select the con tinent";
        //    sheet.AutoFitColumn(4);
        //    sheet.AutoFitColumn(5);

        //    //Style
        //    sheet.Range["D8:D12"].Style.Font.IsBold = true;
        //    sheet.Range["D8"].Style.KnownColor = ExcelColors.Color36;
        //    sheet.Range["D10"].Style.KnownColor = ExcelColors.Color36;
        //    sheet.Range["D12"].Style.KnownColor = ExcelColors.Color36;
        //    sheet.Range["E8"].Style.KnownColor = ExcelColors.LightGreen1;
        //    sheet.Range["E10"].Style.KnownColor = ExcelColors.LightGreen1;
        //    sheet.Range["E12"].Style.KnownColor = ExcelColors.LightGreen1;

        //    sheet.SetRowHeight(8, 16);
        //    sheet.SetRowHeight(10, 16);
        //    sheet.SetRowHeight(12, 16);
        
        //}
        

    }
}
